Investors often pay little attention to their spending habits or relate them to the investment portfolio performance needed to support their lifestyles. The issue takes on greater importance during retirement when income generation generally declines greatly. In the February 1991 issue of the AAII Journal, Steven P. Somes examined the spending side of the investment equation. The projected cash flow template presented in this article addresses many of the issues raised in that article and provides additional flexibility and detail available to investors using computerized spreadsheets.
The spreadsheets intent is to show the effects of income versus expenses projected over a specified number of years. The example starts with year 1 and continues for 28 years for a person retiring at age 65, the projection ends at age 93. If you have rosier life expectancy projections, it is easy to extend the analysis for as long as desired by copying additional rows down. Year 0 is the point just prior to retirement. The only value we have for year 0 is the investment portfolio at the time of retirement.
Reading the worksheet from left to right, we see that it first deals with expected income, then expenses, and finally assets. The growth of these items is controlled through the growth rate in row 11. For example, with Social Security payments indexed to inflation you can enter your long-term inflation estimate in cell B11. The spreadsheet will automatically escalate your annual Social Security payout by the growth rate you specify. You can thereby put in various growth estimates for your income, expenses and investment performance and see the long-term impact.
The income section is divided among Social Security income, pension income and investment portfolio growth. The Social Security component assumes a 5% inflation factor, the pension component assumes zero growth, and 9% investment performance is assumed for the investment portfolio balance stored in column K. The income components are totaled in column E.
Expenses, or spending, are tracked in column F and are assumed to grow at the same 5% estimated inflation rate given for Social Security. Of course, you can change this to any rate that you feel is appropriate. Examining the column, you can see that there are two points in time where sudden changes are made to spending. The first is in year 11 when the mortgage will no longer need to be paid and the second is in year 18 when living expenses are assumed to drop due to a home change. The drops are entered by adding the amount of the change to the standard formula for that year.
By adding total income and expense in the balance column (H), an estimate of net spending or savings for a given year is made. A positive figure would indicate a net increase in savings while a negative figure would point to a year in which securities from the portfolio have to be sold.
The portfolio balance is tracked in column K, with an extra column (I) provided for major asset purchases or sales that the standard spending or income column would not cover. Examples of this in the spreadsheet include the car purchases in years 1 and 8 and the net difference between the sale of the old house and purchase of a smaller house in year 17.
Like most spreadsheet templates, the strength of the program rests with the ability to change assumptions and measure their impact. By changing your growth rate assumptions, income estimates, spending estimates, and portfolio values, you can see whether you would outlive your savings. For investors who would like to do this type of analysis but not mess with spreadsheets, financial planning programs such as WealthBuilder by Money Magazine can also be used. Pat Daugherty is a member of AAII and an avid spreadsheet user.
